#region Copyright (C) 2005-2008 Team MediaPortal

/* 
 *	Copyright (C) 2005-2008 Team MediaPortal
 *	http://www.team-mediaportal.com
 *
 *  This Program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2, or (at your option)
 *  any later version.
 *   
 *  This Program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 *  GNU General Public License for more details.
 *   
 *  You should have received a copy of the GNU General Public License
 *  along with GNU Make; see the file COPYING.  If not, write to
 *  the Free Software Foundation, 675 Mass Ave, Cambridge, MA 02139, USA. 
 *  http://www.gnu.org/copyleft/gpl.html
 *
 */

#endregion

using System;
using System.Collections;
using System.Globalization;
using System.IO;
using SQLite.NET;
using MediaPortal.Util;
using MediaPortal.GUI.Library;
using MediaPortal.Database;
using MediaPortal.Configuration;
using System.Collections.Generic;

namespace MP_Syndication
{
    /// <summary>
    /// Summary description for Database.
    /// </summary>
    public class SyndicationDatabase : IDisposable
    {
        bool disposed = false;
        SQLiteClient m_db = null;

        public SyndicationDatabase()
        {

            Open();
        }

        void Open()
        {
            lock (typeof(SyndicationDatabase))
            {
                Log.Info("opening Syndication database");
                try
                {
                    // Open database
                    try
                    {
                        System.IO.Directory.CreateDirectory(Config.GetFolder(Config.Dir.Database));
                    }
                    catch (Exception) { }
                    m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "Syndication.db3"));

                    DatabaseUtility.SetPragmas(m_db);
                    CreateTables();

                }
                catch (Exception ex)
                {
                    Log.Error("Syndication database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                Log.Info("Syndication database opened");
            }
        }

        bool CreateTables()
        {
            lock (typeof(SyndicationDatabase))
            {
                if (m_db == null) return false;
                
                // Create feeds table
                DatabaseUtility.AddTable(m_db, "feeds", "CREATE TABLE feeds " +
                                                        "( feedId integer primary key, " +
                                                        "  feedName text, " + 
                                                        "  feedUrl text, " + 
                                                        "  feedImage text, " +
                                                        "  nbItems integer, " +
                                                        "  tickerEnabled integer, " +
                                                        "  tickerProperty text, " +
                                                        "  tickerSeparator text, " +
                                                        "  refreshEnabled integer, " +
                                                        "  refreshInterval integer)");
                
                return true;
            }
        }

        /// <summary>
        /// Adds a feed to the database
        /// </summary>
        /// <param name="feed"></param>
        /// <returns></returns>
        public int addFeed(ref Feed feed)
        {
            if (feed == null) return -1;

            // if feedId set the databse already contains this feed
            if (feed.FeedId != -1) return -1;
            lock(typeof(SyndicationDatabase)){
                int lFeedId = -1;
                SQLiteResultSet results;
                try{
                    string feedNameFiltered = feed.FeedName;
                    DatabaseUtility.RemoveInvalidChars(ref feedNameFiltered);

                    string feedUrlFiltered = feed.FeedUrl;
                    DatabaseUtility.RemoveInvalidChars(ref feedUrlFiltered);

                    string imagePathFiltered = feed.ImagePath;
                    DatabaseUtility.RemoveInvalidChars(ref imagePathFiltered);

                    string propertyFiltered = feed.Property;
                    DatabaseUtility.RemoveInvalidChars(ref propertyFiltered);

                    string separatorFiltered = feed.Separator;
                    DatabaseUtility.RemoveInvalidChars(ref separatorFiltered);

                    string strSQL = String.Format("insert into feeds(feedId, feedName, feedUrl, feedImage, nbItems, tickerEnabled," +
                                                  "tickerProperty, tickerSeparator, refreshEnabled, refreshInterval)" +
                                                  "values(null, '{0}', '{1}', '{2}', {3}, {4}," +
                                                  "       '{5}', '{6}', {7}, {8})",
                                                  feedNameFiltered, feedUrlFiltered, imagePathFiltered, feed.NbItems, feed.TickerEnabledAsInt,
                                                  propertyFiltered, separatorFiltered, feed.AutoRefreshEnabledAsInt, feed.AutoRefreshInterval);


                    results = m_db.Execute(strSQL);
                    lFeedId = m_db.LastInsertID();
                    feed.FeedId = lFeedId;
                    return lFeedId;
                }
                catch (Exception ex)
                {
                    Log.Error("Syndication Database exception adding feed err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return -1;
            }
        }

        public void deleteFeed(int feedId)
        {
            lock (typeof(SyndicationDatabase))
            {
                if (m_db == null) return;
                string strSQL = "";
                try
                {
                    strSQL = String.Format("delete from feeds where feedId = {0}", feedId);
                    m_db.Execute(strSQL);
                }
                catch (Exception ex)
                {
                    Log.Error("Syndication Database exception deleting feed err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return;
            }
        }

        public int getFeeds(ref List<Feed> Feeds)
        {
            int Count = 0;
            lock (typeof(SyndicationDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select feedId, feedName, feedUrl, feedImage, nbItems, tickerEnabled," +
                                "tickerProperty, tickerSeparator, refreshEnabled, refreshInterval "+
                                "from feeds order by 2";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        for (Count = 0; Count < result.Rows.Count; Count++)
                        {
                            Feed currentFeed = new Feed(DatabaseUtility.Get(result, Count, 1),//feedName
                                                        DatabaseUtility.Get(result, Count, 2),//feedUrl
                                                        int.Parse(DatabaseUtility.Get(result, Count, 4)),//nbItems
                                                        DatabaseUtility.Get(result, Count, 3),//imagePath
                                                        DatabaseUtility.Get(result, Count, 5).Equals("1"),//tickerEnabled
                                                        DatabaseUtility.Get(result, Count, 7),//separator
                                                        DatabaseUtility.Get(result, Count, 6),//property
                                                        DatabaseUtility.Get(result, Count, 8).Equals("1"),//autoRefreshEnabled
                                                        int.Parse(DatabaseUtility.Get(result, Count, 9)));//autoRefreshInterval
                            currentFeed.FeedId = int.Parse(DatabaseUtility.Get(result, Count, 0));//feedId;
                            Feeds.Add(currentFeed);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("Syndication Database exception getting Feeds err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        }


        /*
        public int AddPicture(string strPicture, int iRotation)
        {
            if (strPicture == null) return -1;
            if (strPicture.Length == 0) return -1;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return -1;
                string strSQL = "";
                try
                {
                    int lPicId = -1;
                    SQLiteResultSet results;
                    string strPic = strPicture;
                    string strDateTaken = "";
                    DatabaseUtility.RemoveInvalidChars(ref strPic);

                    strSQL = String.Format("select * from picture where strFile like '{0}'", strPic);
                    results = m_db.Execute(strSQL);
                    if (results != null && results.Rows.Count > 0)
                    {
                        lPicId = System.Int32.Parse(DatabaseUtility.Get(results, 0, "idPicture"));
                        return lPicId;
                    }
                    //Changed mbuzina
                    using (ExifMetadata extractor = new ExifMetadata())
                    {
                        ExifMetadata.Metadata metaData = extractor.GetExifMetadata(strPic);
                        try
                        {
                            //Exception here!!! (very bad since the insert doesn't happen then..
                            //  DateTimeFormatInfo dateTimeFormat = new DateTimeFormatInfo();
                            //  dateTimeFormat.ShortDatePattern = "yyyy:MM:dd HH:mm:ss";
                            string picExifDate = metaData.DatePictureTaken.DisplayValue;
                            //DateTime dat = DateTime.ParseExact(picExifDate, "d", dateTimeFormat);

                            DateTime dat;
                            DateTimeStyles mpPicStyle;
                            mpPicStyle = DateTimeStyles.None;
                            if (picExifDate != null) // If the image contains a valid exif date store it in the database, otherwise use the file date
                            {
                                DateTime.TryParseExact(picExifDate, "G", System.Threading.Thread.CurrentThread.CurrentCulture, mpPicStyle, out dat);
                                strDateTaken = dat.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else
                            {
                                try
                                {
                                    dat = File.GetLastWriteTime(strPicture);
                                    if (!TimeZone.CurrentTimeZone.IsDaylightSavingTime(dat)) dat = dat.AddHours(1); // Try to respect the timezone of the file date
                                    strDateTaken = dat.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                catch (Exception ex)
                                {
                                    Log.Error("file date conversion exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                                }
                            }
                        }
                        catch (System.FormatException ex)
                        {
                            Log.Error("date conversion exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                        }
                        iRotation = EXIFOrientationToRotation(Convert.ToInt32(metaData.Orientation.Hex));
                    }
                    if (File.Exists(Path.GetDirectoryName(strPic) + "\\Picasa.ini"))
                    {
                        using (StreamReader sr = File.OpenText(Path.GetDirectoryName(strPic) + "\\Picasa.ini"))
                        {
                            try
                            {
                                string s = "";
                                bool searching = true;
                                while ((s = sr.ReadLine()) != null && searching)
                                {
                                    if (s.ToLower() == "[" + Path.GetFileName(strPic).ToLower() + "]")
                                    {
                                        do
                                        {
                                            s = sr.ReadLine();
                                            if (s.StartsWith("rotate=rotate("))
                                            {
                                                // Find out Rotate Setting
                                                try
                                                {
                                                    iRotation = int.Parse(s.Substring(14, 1));
                                                }
                                                catch (Exception ex)
                                                {
                                                    Log.Error("MyPictures: error converting number picasa.ini", ex.Message, ex.StackTrace);
                                                }
                                                searching = false;
                                            }
                                        } while (s != null && !s.StartsWith("[") && searching);
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                Log.Error("MyPictures: file read problem picasa.ini", ex.Message, ex.StackTrace);
                            }
                        }
                    }
                    strSQL = String.Format("insert into picture (idPicture, strFile, iRotation, strDateTaken) values(null, '{0}',{1},'{2}')", strPic, iRotation, strDateTaken);
                    //End Changed

                    results = m_db.Execute(strSQL);
                    lPicId = m_db.LastInsertID();
                    return lPicId;
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return -1;
            }
        }

        public void DeletePicture(string strPicture)
        {
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return;
                string strSQL = "";
                try
                {
                    string strPic = strPicture;
                    DatabaseUtility.RemoveInvalidChars(ref strPic);

                    strSQL = String.Format("delete from picture where strFile like '{0}'", strPic);
                    m_db.Execute(strSQL);
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception deleting picture err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return;
            }
        }

        public int GetRotation(string strPicture)
        {
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return -1;
                string strSQL = "";
                try
                {
                    SQLiteResultSet results;
                    string strPic = strPicture;
                    int iRotation;
                    DatabaseUtility.RemoveInvalidChars(ref strPic);

                    strSQL = String.Format("select * from picture where strFile like '{0}'", strPic);
                    results = m_db.Execute(strSQL);
                    if (results != null && results.Rows.Count > 0)
                    {
                        iRotation = System.Int32.Parse(DatabaseUtility.Get(results, 0, "iRotation"));
                        return iRotation;
                    }

                    ExifMetadata extractor = new ExifMetadata();
                    ExifMetadata.Metadata metaData = extractor.GetExifMetadata(strPicture);
                    iRotation = EXIFOrientationToRotation(Convert.ToInt32(metaData.Orientation.Hex));

                    AddPicture(strPicture, iRotation);
                    return iRotation;
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return 0;
            }
        }

        public void SetRotation(string strPicture, int iRotation)
        {
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return;
                string strSQL = "";
                try
                {
                    SQLiteResultSet results;
                    string strPic = strPicture;
                    DatabaseUtility.RemoveInvalidChars(ref strPic);

                    long lPicId = AddPicture(strPicture, iRotation);
                    if (lPicId >= 0)
                    {
                        strSQL = String.Format("update picture set iRotation={0} where strFile like '{1}'", iRotation, strPic);
                        results = m_db.Execute(strSQL);
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
            }
        }

        //Changed mbuzina
        public DateTime GetDateTaken(string strPicture)
        {
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return DateTime.MinValue;
                string strSQL = "";
                try
                {
                    SQLiteResultSet results;
                    string strPic = strPicture;
                    string strDateTime;
                    DatabaseUtility.RemoveInvalidChars(ref strPic);

                    strSQL = String.Format("select * from picture where strFile like '{0}'", strPic);
                    results = m_db.Execute(strSQL);
                    if (results != null && results.Rows.Count > 0)
                    {
                        strDateTime = DatabaseUtility.Get(results, 0, "strDateTaken");
                        if (strDateTime != string.Empty && strDateTime != "")
                        {
                            DateTime dtDateTime = DateTime.ParseExact(strDateTime, "yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo(""));
                            return dtDateTime;
                        }
                    }
                    AddPicture(strPicture, -1);
                    using (ExifMetadata extractor = new ExifMetadata())
                    {
                        ExifMetadata.Metadata metaData = extractor.GetExifMetadata(strPic);
                        strDateTime = System.DateTime.Parse(metaData.DatePictureTaken.DisplayValue).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    if (strDateTime != string.Empty && strDateTime != "")
                    {
                        DateTime dtDateTime = DateTime.ParseExact(strDateTime, "yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo(""));
                        return dtDateTime;
                    }
                    else
                    {
                        return DateTime.MinValue;
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return DateTime.MinValue;
            }
        }
        //End Changed

        public int EXIFOrientationToRotation(int orientation)
        {
            Log.Info("Orientation: {0}", orientation);

            if (orientation == 6)
                return 1;

            if (orientation == 3)
                return 2;

            if (orientation == 8)
                return 3;

            return 0;
        }

        public int ListYears(ref List<string> Years)
        {
            int Count = 0;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select distinct substr(strDateTaken,1,4) from picture order by 1";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        for (Count = 0; Count < result.Rows.Count; Count++)
                            Years.Add(DatabaseUtility.Get(result, Count, 0));
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception getting Years err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        }

        public int ListMonths(string Year, ref List<string> Months)
        {
            int Count = 0;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select distinct substr(strDateTaken,6,2) from picture where strDateTaken like '" + Year + "%' order by 1";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        for (Count = 0; Count < result.Rows.Count; Count++)
                            Months.Add(DatabaseUtility.Get(result, Count, 0));
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception getting Months err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        }

        public int ListDays(string Month, string Year, ref List<string> Days)
        {
            int Count = 0;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select distinct substr(strDateTaken,9,2) from picture where strDateTaken like '" + Year + "-" + Month + "%' order by 1";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        for (Count = 0; Count < result.Rows.Count; Count++)
                            Days.Add(DatabaseUtility.Get(result, Count, 0));
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception getting Days err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        }

        public int ListPicsByDate(string Date, ref List<string> Pics)
        {
            int Count = 0;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select strFile from picture where strDateTaken like '" + Date + "%' order by 1";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        for (Count = 0; Count < result.Rows.Count; Count++)
                            Pics.Add(DatabaseUtility.Get(result, Count, 0));
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception getting Picture by Date err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        }

        public int CountPicsByDate(string Date)
        {
            int Count = 0;
            lock (typeof(PictureDatabase))
            {
                if (m_db == null) return 0;
                string strSQL = "select count(strFile) from picture where strDateTaken like '" + Date + "%' order by 1";
                SQLiteResultSet result;
                try
                {
                    result = m_db.Execute(strSQL);
                    if (result != null)
                    {
                        Count = DatabaseUtility.GetAsInt(result, 0, 0);
                    }
                }
                catch (Exception ex)
                {
                    Log.Error("MediaPortal.Picture.Database exception getting Picture by Date err:{0} stack:{1}", ex.Message, ex.StackTrace);
                    Open();
                }
                return Count;
            }
        } */

        #region IDisposable Members

        public void Dispose()
        {
            if (!disposed)
            {
                disposed = true;
                if (m_db != null)
                {
                    try
                    {
                        m_db.Close();
                        m_db.Dispose();
                    }
                    catch (Exception) { }
                    m_db = null;
                }
            }
        }

        #endregion
    }
}